import matplotlib.pyplot as plt
import numpy as np
import pymysql

if __name__ == '__main__':
    conn = pymysql.connect(host='localhost',  # 连接名称
                           user='root',  # 用户名
                           passwd='q19723011',  # 密码
                           port=3306,  # 端口，默认为3306
                           db='month6',  # 数据库
                           charset='utf8',  # 字符编码
                           )
    cur = conn.cursor()  # 生成游标对象
    sql = "select * from line_time_flow where time >= %s and  time <= %s and lineID = %d and date =%s" \
          % ("70000", "230000", 1, "20180601")
    cur.execute(sql)
    datas = np.array(list(cur.fetchall()))[:, 3:].tolist()
    y_1 = [i[0] for i in datas]

    sql = "select * from line_time_flow where time >= %s and  time <= %s and lineID = %d and date =%s" \
          % ("70000", "230000", 1, "20180602")
    cur.execute(sql)
    datas = np.array(list(cur.fetchall()))[:, 3:].tolist()
    y_2 = [i[0] for i in datas]

    sql = "select * from line_time_flow where time >= %s and  time <= %s and lineID = %d and date =%s" \
          % ("70000", "230000", 1, "20180603")
    cur.execute(sql)
    datas = np.array(list(cur.fetchall()))[:, 3:].tolist()
    y_3 = [i[0] for i in datas]

    sql = "select * from line_time_flow where time >= %s and  time <= %s and lineID = %d and date =%s" \
          % ("70000", "230000", 1, "20180604")
    cur.execute(sql)
    datas = np.array(list(cur.fetchall()))[:, 3:].tolist()
    y_4 = [i[0] for i in datas]

    sql = "select * from line_time_flow where time >= %s and  time <= %s and lineID = %d and date =%s" \
          % ("70000", "230000", 1, "20180605")
    cur.execute(sql)
    datas = np.array(list(cur.fetchall()))[:, 3:].tolist()
    y_5 = [i[0] for i in datas]

    sql = "select * from line_time_flow where time >= %s and  time <= %s and lineID = %d and date =%s" \
          % ("70000", "230000", 1, "20180606")
    cur.execute(sql)
    datas = np.array(list(cur.fetchall()))[:, 3:].tolist()
    y_6 = [i[0] for i in datas]

    sql = "select * from line_time_flow where time >= %s and  time <= %s and lineID = %d and date =%s" \
          % ("70000", "230000", 1, "20180607")
    cur.execute(sql)
    datas = np.array(list(cur.fetchall()))[:, 3:].tolist()
    y_7 = [i[0] for i in datas]

    conn.commit()
    cur.close()  # 关闭游标
    conn.close()  # 关闭连接
    print(y_1)
    print(y_2)
    x = [i for i in range(len(y_7))]

    # 创建画布
    plt.figure()

    # '''绘制第一条数据线
    # 1、节点为圆圈
    # 2、线颜色为红色
    # 3、标签名字为y1-data
    # '''
    # plt.plot(x, y_1, marker='o', color='r', label='y1-data')
    #
    # '''绘制第二条数据线
    # 1、节点为五角星
    # 2、线颜色为蓝色
    # 3、标签名字为y2-data
    # '''
    # plt.plot(x, y_2, marker='*', color='b', label='y2-data')

    y_1 = y_3
    plt.plot(x, y_1, label='20180601')
    plt.plot(x, y_2,label='20180602')
    plt.plot(x, y_3, label='20180603')
    plt.plot(x, y_4, label='20180604')
    plt.plot(x, y_5, label='20180605')
    plt.plot(x, y_6, label='20180606')
    plt.plot(x, y_7, label='20180607')


    # 显示图例（使绘制生效）
    plt.legend()

    # 横坐标名称
    plt.xlabel("time")

    # 纵坐标名称
    plt.ylabel("flow")

    # 保存图片到本地
    plt.savefig('pci.png')

    # 显示图片
    plt.show()
